﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace jlynet.core.notebook.util
{
    class MySQLiteHelper
    {

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <returns></returns>
        public static bool CreateDB()
        {
            try
            {
                if (File.Exists(SQLiteConfigHelper.DatabaseFile))
                {
                    File.Delete(@"" + SQLiteConfigHelper.DatabaseFile);
                }
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    conn.Open();
                    if (SQLiteConfigHelper.isPassword)
                    {
                        conn.ChangePassword(SQLiteConfigHelper.ChangePassword);
                    }
                    conn.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("创建数据库错误：{0}", ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 创建数据库表
        /// 主键
        /// tb.Columns.Add(new SQLiteColumn("id", ColType.Integer, true, true, true, ""));
        /// 非主键
        /// tb.Columns.Add(new SQLiteColumn("host_", ColType.Text, false, false, true, ""));
        /// string colName, ColType colDataType, bool primaryKey, bool autoIncrement, bool notNull, string defaultValue
        /// </summary>
        /// <param name="Column"></param>
        /// <returns></returns>
        public static bool CreateTable(List<SQLiteColumn> Column)
        {
            try
            {
                // Creating table....
                SQLiteTable tb = new SQLiteTable(SQLiteConfigHelper.tableName);

                foreach (SQLiteColumn c in Column)
                {
                    tb.Columns.Add(c);
                }
                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        sh.BeginTransaction();
                        try
                        {
                            sh.DropTable(SQLiteConfigHelper.tableName);
                            sh.CreateTable(tb);
                            sh.Commit();//递交事物
                            conn.Close();
                            return true;
                        }
                        catch (Exception ex1)
                        {
                            //事物回滚
                            sh.Rollback();
                            //LoadData(sh);
                            conn.Close();
                            Console.WriteLine("创建数据库表错误：{0}", ex1.ToString());
                            return false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("创建数据库表错误：{0}", ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 执行sql语句 并得到行信息 禁用事物
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long executionSQL(string sql)
        {
            return executionSQL(sql,false);
        }
        
        /// <summary>
        /// 执行sql语句 并得到行信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long executionSQL(string sql, bool isTran)
        {
            try
            {
                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    if (SQLiteConfigHelper.isPassword)
                    {
                        conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                    }
                    conn.Open();//打开连接 
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            object obj = sh.ExecuteScalar(sql);
                            //rows = Convert.ToInt32(obj);
                            long rows = sh.LastInsertRowId();
                            if (isTran)
                            {
                                sh.Commit();//提交事物
                            }
                            conn.Close();
                            return rows;
                        }
                        catch (Exception ex1)
                        {
                            Console.WriteLine("执行sql错误：{0}", ex1.ToString());
                            if (isTran)
                            {
                                sh.Rollback();//事物回滚
                            }
                            return -1L;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行sql错误：{0}", ex.ToString());
                return -1L;
            }
        }

        /// <summary>
        /// 执行sql语句 并得到行信息 禁用事物
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static long executionSQL(string sql, SQLiteParameter[] values)
        {
            return executionSQL(sql, values,false);
        }

        /// <summary>
        /// 执行sql语句 并得到行信息
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <param name="file"></param>
        /// <returns></returns>
        public static long executionSQLFile(string sql, SQLiteParameter[] values, string file)
        {
            try
            {
				//存储
				/* Bitmap bmp = (((BitmapDrawable)tmp.image).getBitmap());
				ByteArrayOutputStream os = new ByteArrayOutputStream();
				bmp.compress(Bitmap.CompressFormat.PNG, 100, os);
				ContentValues values = new ContentValues();
				values.put("image", os.toByteArray());
				db.insert("apps", null, values);
				db.close(); */
				
				//读取
				/* byte[] blob = cursor.getBlob(cursor.getColumnIndex("image"));
				Bitmap bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
				BitmapDrawable bd = new BitmapDrawable(bmp); */
				
				
                SQLiteParameter para = new SQLiteParameter("@data", DbType.Binary);
                //将要保存图片的字段类型设为blob
                //string file = @"F:/Image/飞机.png";
                FileStream fs = new FileStream(file, FileMode.Open);

                //StreamUtil su = new StreamUtil();  
                //byte[] buffer = su.StreamToBytes(fs);  
                byte[] buffer = StreamUtil.ReadFully(fs);
                fs.Close();
                para.Value = buffer;
                values.Contains(para);
                return executionSQL(sql, values, false);
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行sql错误：{0}", ex.Message);
            }
            return -1L;
        }

        /// <summary>
        /// 执行sql语句 并得到行信息
        /// MySQLiteHelper.executionSQL("INSERT INTO `problemTable` (id_,title,update_time,create_time) VALUES (?,?,?,?)", new SQLiteParameter[] { SQLiteUtil.CreateSqliteParameter("id_", DbType.String, 4, 112), SQLiteUtil.CreateSqliteParameter("title", DbType.String, 10, "Hello 11") , SQLiteUtil.CreateSqliteParameter("update_time", DbType.Int32, 100, 111111) , SQLiteUtil.CreateSqliteParameter("create_time", DbType.Int32, 100, 222222) });
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static long executionSQL(string sql, SQLiteParameter[] values, bool isTran)
        {
            try
            {
                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    if (SQLiteConfigHelper.isPassword)
                    {
                        conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                    }
                    conn.Open();//打开连接 
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            object obj = sh.ExecuteScalar(sql, values);
                            long rows = sh.LastInsertRowId();
                            //rows = Convert.ToInt32(obj);
                            if (isTran)
                            {
                                sh.Commit();//提交事物
                            }
                            conn.Close();
                            return rows;
                        }
                        catch (Exception ex1)
                        {
                            Console.WriteLine("执行sql错误：{0}", ex1.ToString());
                            if (isTran)
                            {
                                sh.Rollback();//事物回滚
                            }
                            return -1L;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行sql错误：{0}", ex.ToString());
                return -1L;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int SelectSQL(string sql)
        {
            int rows = 0;
            try
            {
                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    if (SQLiteConfigHelper.isPassword)
                    {
                        conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                    }
                    conn.Open();//打开连接 
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        cmd.CommandText = sql;
                        cmd.CommandType = CommandType.Text;
                        try
                        {
                            cmd.ExecuteNonQuery();//执行查询
                            conn.Close();
                        }
                        catch (Exception ex1)
                        {
                            rows = 0;
                            Console.WriteLine("执行sql错误：{0}", ex1.ToString());
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                rows = 0;
                Console.WriteLine("执行sql错误：{0}", ex.ToString());
            }
            return rows;
        }

        /// <summary>
        /// 查询数据
        /// rows = MySQLiteHelper.SelectSQL("select * from userInfoTable where password= ? and file_path= ?;", new SQLiteParameter[] { new SQLiteParameter("password", password), new SQLiteParameter("file_path", parm["filename"]) });
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int SelectSQL(string sql, SQLiteParameter[] values)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        try
                        {
                            SQLiteHelper sh = new SQLiteHelper(cmd);
                            DataTable dt = sh.Select(sql, values);
                            DataRowCollection Rows = dt.Rows;
                            return Rows.Count;
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("数据查询错误：{0}", ex.ToString());
                            return -1;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据查询错误：{0}", ex.ToString());
                return -1;
            }
        }

        /// <summary>
        /// 执行查询返回DataSet
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>返回DataSet</returns>
        public static DataSet selectSQLDataSet(string sqlStr)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    if (SQLiteConfigHelper.isPassword)
                    {
                        conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                    }
                    conn.Open();
                    SQLiteCommand comm = new SQLiteCommand(conn);
                    comm.CommandText = sqlStr;
                    comm.CommandType = CommandType.Text;
                    DataSet ds = new DataSet();
                    SQLiteDataAdapter da = new SQLiteDataAdapter(comm);
                    da.Fill(ds);
                    //conn.Close();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行sql错误：{0}", ex.ToString());
            }
            return null;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataView SelectDataView(string sql)
        {
            return SelectDataView(sql, new SQLiteParameter[]{ });
        }

        /// <summary>
        /// 查询数据
        /// MySQLiteHelper.SelectDataView("select * from userInfoTable where password= ? and file_path= ?;", new SQLiteParameter[] { new SQLiteParameter("password", password), new SQLiteParameter("file_path", parm["filename"]) });
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataView SelectDataView(string sql, SQLiteParameter[] values)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        try
                        {
                            SQLiteHelper sh = new SQLiteHelper(cmd);
                            DataTable dt = null;
                            if (values!=null && values.Length>0)
                            {
                                dt = sh.Select(sql, values);
                            }
                            else
                            {
                                dt = sh.Select(sql);
                            }
                            DataView myvw = dt.DefaultView;
                            conn.Close();
                            return myvw;
                        }
                        catch (Exception ex)
                        {
                            DataTable dt = new DataTable();
                            dt.Columns.Add("Error");
                            dt.Rows.Add(ex.ToString());
                            DataView myvw = dt.DefaultView;
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return myvw;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据查询错误：{0}", ex.ToString());
                return null;
            }
        }

        /// <summary>
        ///  查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable SelectDataTable(string sql)
        {
            return SelectDataTable(sql, new SQLiteParameter[] { });
        }

        /// <summary>
        /// 查询数据
        /// MySQLiteHelper.SelectDataTable("select * from userInfoTable where password= ? and file_path= ?;", new SQLiteParameter[] { new SQLiteParameter("password", password), new SQLiteParameter("file_path", parm["filename"]) });
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable SelectDataTable(string sql, SQLiteParameter[] values)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        try
                        {
                            SQLiteHelper sh = new SQLiteHelper(cmd);
                            DataTable dt = null;
                            if (values != null && values.Length > 0)
                            {
                                dt = sh.Select(sql, values);
                            }
                            else
                            {
                                dt = sh.Select(sql);
                            }
                            conn.Close();
                            return dt;
                        }
                        catch (Exception ex)
                        {
                            DataTable dt = new DataTable();
                            dt.Columns.Add("Error");
                            dt.Rows.Add(ex.ToString());
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return dt;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据查询错误：{0}", ex.ToString());
                return null;
            }
        }

        /// <summary>
        ///  新增多个对象 禁用事物
        /// </summary>
        /// <param name="arrayList"></param>
        /// <param name="isTran"></param>
        /// <returns></returns>
        public static long[] InsertArrayList(ArrayList arrayList)
        {
            return InsertArrayList( arrayList,false);
        }

        /// <summary>
        /// 新增多个对象
        /// </summary>
        /// <param name="arrayList">Dictionary<string, object></param>
        /// <returns></returns>
        public static long[] InsertArrayList(ArrayList arrayList, bool isTran)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            long[] rows_ = new long[arrayList.Count];
                            int i = 0;
                            foreach (Dictionary<string, object> dic in arrayList)
                            {
                                sh.Insert(SQLiteConfigHelper.tableName, dic);
                                long rows = sh.LastInsertRowId();
                                rows_[i] = rows;
                                i++;
                            }
                            if (isTran)
                            {
                                sh.Commit();//递交事物
                            }
                            conn.Close();
                            return rows_;
                        }
                        catch (Exception ex)
                        {
                            if (isTran)
                            {
                                sh.Rollback();//事物回滚
                            }
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据插入错误：{0}", ex.ToString());
                return null;
            }
        }

        /// <summary>
        ///  新增单个对象 禁用事物
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static long Insert(Dictionary<string, object> dic)
        {
            return Insert(dic, false);
        }

        /// <summary>
        /// 新增单个对象
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static long Insert(Dictionary<string, object> dic, bool isTran)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            sh.Insert(SQLiteConfigHelper.tableName, dic);
                            long rows = sh.LastInsertRowId();
                            if (isTran)
                            {
                                sh.Commit();//递交事物
                            }
                            conn.Close();
                            return rows;
                        }
                        catch (Exception ex)
                        {
                            if (isTran)
                            {
                                sh.Rollback();//事物回滚
                            }
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return -1L;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据插入错误：{0}", ex.ToString());
                return -1L;
            }
        }

        /// <summary>
        ///  修改多个对象 禁用事物
        /// </summary>
        /// <param name="arrayList1"></param>
        /// <param name="arrayList2"></param>
        /// <returns></returns>
        public static long[] UpdateArrayList(ArrayList arrayList1, ArrayList arrayList2)
        {
            return UpdateArrayList( arrayList1, arrayList2,false);
        }

        /// <summary>
        /// 修改多个对象
        /// </summary>
        /// <param name="arrayList1"> 如果字典有值，则认为多个where条件集合</param>
        /// <param name="arrayList2">需要改的字段与字段值得集合</param>
        /// <returns></returns>
        public static long[] UpdateArrayList(ArrayList arrayList1, ArrayList arrayList2, bool isTran)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        cmd.Connection = conn;
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            long[] rows_ = new long[arrayList1.Count];
                            for (int i = 0; i < arrayList1.Count; i++)
                            {
                                Dictionary<string, object> dicData = (Dictionary<string, object>)arrayList1[i];
                                Dictionary<string, object> dicCond = (Dictionary<string, object>)arrayList2[i];
                                sh.Update(SQLiteConfigHelper.tableName, dicData, dicCond);
                                long rows = sh.LastInsertRowId();
                                rows_[i] = rows;
                            }
                            if (isTran)
                            {
                                sh.Commit();
                            }
                            conn.Close();
                            return rows_;
                        }
                        catch (Exception ex)
                        {
                            if (isTran)
                            {
                                sh.Rollback();
                            }
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据更新错误：{0}", ex.ToString());
                return null;
            }
        }

        /// <summary>
        ///  修改单个对象 禁用事物
        /// </summary>
        /// <param name="dicData"></param>
        /// <param name="dicCond"></param>
        /// <returns></returns>
        public static long Update(Dictionary<string, object> dicData, Dictionary<string, object> dicCond)
        {
            return Update(dicData, dicCond,false);
        }

        /// <summary>
        /// 修改单个对象
        /// </summary>
        /// <param name="dicData"></param>
        /// <param name="dicCond"></param>
        /// <returns></returns>
        public static long Update(Dictionary<string, object> dicData, Dictionary<string, object> dicCond, bool isTran)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        cmd.Connection = conn;
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if (isTran)
                        {
                            sh.BeginTransaction();
                        }
                        try
                        {
                            sh.Update(SQLiteConfigHelper.tableName, dicData, dicCond);
                            long rows = sh.LastInsertRowId();
                            if (isTran)
                            {
                                sh.Commit();
                            }
                            conn.Close();
                            return rows;
                        }
                        catch (Exception ex)
                        {
                            if (isTran)
                            {
                                sh.Rollback();
                            }
                            conn.Close();
                            Console.WriteLine("数据更新错误：{0}", ex.ToString());
                            return -1L;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("数据更新错误：{0}", ex.ToString());
                return -1L;
            }
        }

        /// <summary>
        /// 执行压缩数据库
        /// </summary>
        public static void ExecuteZip()
        {
            using (SQLiteConnection conn = new SQLiteConnection(SQLiteConfigHelper.DataSource))
            {
                using (SQLiteCommand cmd = new SQLiteCommand("VACUUM", conn))
                {
                    try
                    {
                        if (SQLiteConfigHelper.isPassword)
                        {
                            conn.SetPassword(SQLiteConfigHelper.ChangePassword);
                        }
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        conn.Close();
                        Console.WriteLine("数据更新错误：{0}", E.ToString());
                    }
                }
            }
        }


    }
}
